capture log close
clear all
set maxvar 10000
set more off
pause off
# delimit;

local crime "";
local output "";
local zip "" ;

/* 
Variation in RD estimates by UE rate, min wage, cost of living 
*/


local exp "prworavary";

local controls_all "n_male i.n_firstage i.n_diag1cat n_dibmdr_mie n_dibmdr_mine n_dibmdr_miss n_fam_singmom n_fam_nopar i.n_earnhhbins" ; /*  i.state */
local controls_no  "" ; 

local bwidth_list "264" ;
local cov_list   "all no" ; 

local poly_list "1(1)1" ;


/* ************************************************************** */
/* **** PREP COST OF LIVING DATA (BEA Regional Price Parity) **** */
/* ************************************************************** */

import excel "`crime'\\BEA_RPP_byMSA_2009_2019.xls", clear firstrow ;
forval yr = 2008(1)2019 { ;
	capture destring rpp`yr', force replace ;
} ;
keep if Description == "RPPs: All items" ;
rename GeoFips cbsa ;
keep cbsa rpp* ;
tempfile rpp ;
save `rpp' ;

use "`crime'\cbsa2fipsxw.dta", clear ;
rename cbsacode cbsa ;
rename fipsstatecode fips_state ;
rename fipscountycode fips_cnty ;
destring fips_state, replace ;
destring fips_cnty, replace ;
tempfile cbsa2fipsxw ;
save `cbsa2fipsxw' ;


/* ************************************************ */
/* **** PREP UNEMPLOYMENT RATE DATA (BLS LAUS) **** */
/* ************************************************ */

use "`zip'\pdscc_fips.dta", clear ;
drop if pdscc_num == "" ;
destring pdscc_num, gen(temp) ;
gen str5 pdscc = string(temp,"%05.0f") ;
tempfile pdscc ;
save `pdscc' ;

forval yr = 97(1)99 { ;
	import excel "`crime'\laucnty`yr'.xlsx", clear firstrow ;
	keep fips_state fips_cnty urate ;
	capture destring urate, force replace ;
	rename urate urate19`yr' ;
	tempfile ue19`yr' ;
	save `ue19`yr'' ;
} ;

forval yr = 0(1)9 { ;
	import excel "`crime'\laucnty0`yr'.xlsx", clear firstrow ;
	keep fips_state fips_cnty urate ;
	capture destring urate, force replace ;
	rename urate urate200`yr' ;
	tempfile ue200`yr' ;
	save `ue200`yr'' ;
} ;

forval yr = 10(1)17 { ;
	import excel "`crime'\laucnty`yr'.xlsx", clear firstrow ;
	keep fips_state fips_cnty urate ;
	capture destring urate, force replace ;
	rename urate urate20`yr' ;
	tempfile ue20`yr' ;
	save `ue20`yr'' ;
} ;

use `ue1997', clear ;
forval yr = 1998(1)2017 { ;
	merge 1:1 fips_state fips_cnty using `ue`yr'' ;
	drop _merge ;
} ;
tempfile urate ;
save `urate' ;


/* County population */
use "`crime'\avg_county_pop_1990_2000.dta", clear ;
rename event_county old ;
gen str5 event_county = string(old,"%05.0f") ;
gen fips_cnty = substr(event_county,-3,3) ;
gen fips_state = substr(event_county,1,2) ;
destring fips_cnty, replace ;
destring fips_state, replace ;
rename pop1990 cnty_pop1990 ;
rename pop2000 cnty_pop2000 ;
duplicates report fips_cnty fips_state ;
tempfile pop ;
save `pop' ; 




/* ******************************************************************************************* */
/* **** PREP MINIMUM WAGE DATA (github.com/equitablegrowth/VZ_historicalminwage/releases) **** */
/* ******************************************************************************************* */

use "`crime'\cpi_1990_2020.dta", clear ;
gen temp = cpi if year == 1997 ;
egen cpi_1997 = max(temp) ;
drop temp ;
replace cpi = cpi_1997/cpi ;
rename cpi cpi_alt ;
tempfile cpi ;
save `cpi' ;

use "`crime'\minwage_state_year.dta", clear ;
rename stateabb state ;
replace state = lower(state) ;
egen minwage = rowmax(min_mw min_fed_mw) ;
keep state year minwage ;
reshape wide minwage, i(state) j(year) ;
tempfile minwage_st ;
save `minwage_st' ;

use "`crime'\minwage_county_year.dta", clear ;
rename FIPS fips ;
gen fips_state = substr(fips,1,2) ;
gen fips_cnty  = substr(fips,3,3) ;
destring fips_state, replace ;
destring fips_cnty, replace ;
rename min_mw minwage_local ;
collapse minwage_local, by(fips_state fips_cnty year) ;
keep fips_state fips_cnty year minwage_local ;
reshape wide minwage_local, i(fips_state fips_cnty) j(year) ;
tempfile minwage_local ;
save `minwage_local' ;



/* ************************************** */
/* **** RUN YEAR-BY-YEAR REGRESSIONS **** */
/* ************************************** */

local last_urate = 2017 ;
local last_minwage = 2016 ;
local last_rpp = 2017 ;

use "`crime'\prep2\crime_prwora_prepforreg.dta", clear ;

keep if abs(dobdd_run) <= `bwidth_list' ;

keep if keep_1997_2017_6cg == 1 ;


/* Merge in unemployment rate */
merge m:1 pdscc using `pdscc', keepusing(fips_state fips_cnty) ;
drop if _merge == 2 ;
drop _merge ;

merge m:1 fips_state fips_cnty using `urate', keepusing(urate*) ;
drop if _merge == 2 ;
drop _merge ;

merge m:1 fips_state fips_cnty using `pop', keepusing(cnty_pop1990 cnty_pop2000) ;
drop if _merge == 2 ;
drop _merge ;

/* Merge in cost of living data */
merge m:1 fips_state fips_cnty using `cbsa2fipsxw', keepusing(cbsa) ;
drop if _merge == 2 ;
drop _merge ;

merge m:1 cbsa using `rpp', keepusing(rpp*) ;
drop if _merge == 2 ;
drop _merge ;


/* Merge in minimum wage */
merge m:1 state using `minwage_st', keepusing(minwage*) ;
drop if _merge == 2 ;
drop _merge ;

merge m:1 fips_state fips_cnty using `minwage_local', keepusing(minwage_local*) ;
drop if _merge == 2 ;
drop _merge ;

forval yr = 1997(1)`last_minwage' { ;
	gen 	minwage`yr' = minwage_st`yr' ;
	replace minwage`yr' = minwage_local`yr' if minwage_local`yr' != . ;
} ;


gen cpi1997 = 1.495 ;
gen cpi1998 = 1.472 ;
gen cpi1999 = 1.440 ;
gen cpi2000 = 1.394 ;
gen cpi2001 = 1.355 ;
gen cpi2002 = 1.333 ;
gen cpi2003 = 1.304 ;
gen cpi2004 = 1.271 ;
gen cpi2005 = 1.228 ;
gen cpi2006 = 1.190 ;
gen cpi2007 = 1.157 ;
gen cpi2008 = 1.115 ;
gen cpi2009 = 1.118 ;
gen cpi2010 = 1.100 ;
gen cpi2011 = 1.067 ;
gen cpi2012 = 1.044 ;
gen cpi2013 = 1.029 ;
gen cpi2014 = 1.014 ;
gen cpi2015 = 1.013 ;
gen cpi2016 = 1 	;
gen cpi2017 = .9789 ;

forval yr = 1997(1)`last_minwage' { ;
	replace minwage`yr' = minwage`yr' * cpi`yr' ;
} ; 


/* RD regressions */

gen postXdobdd=n_post*dobdd_run ;

local begin_1 = 1997 ;
local end_1   = 2001 ;
local begin_2 = 2002 ;
local end_2   = 2006 ;
local begin_3 = 2007 ;
local end_3   = 2012 ;
local begin_4 = 2013 ;

foreach rate in urate minwage crimerate { ;

	local end_4   = `last_`rate'' ;

	egen `rate'_9717 = rowmean(`rate'1997-`rate'`last_`rate'') ;
	egen `rate'_0117 = rowmean(`rate'2001-`rate'`last_`rate'') ;

	forval per = 1(1)4 { ;
		egen `rate'_`per' = rowmean(`rate'`begin_`per''-`rate'`end_`per'') ;
	} ;

	foreach var of varlist `rate'_* { ;
		gen postX`var' = n_post * `var' ;
	} ;
	
	foreach var of varlist `rate'_* { ;
		gen unfavX`var' = n_age18unfav * `var' ;
	} ;
} ;

	egen rpp_0817 = rowmean(rpp2008-rpp2017) ;
	egen rpp_3 = rowmean(rpp2008-rpp`end_3') ;
	egen rpp_4 = rowmean(rpp`begin_4'-rpp2017) ;

	foreach var of varlist rpp_* { ;
		gen postX`var' = n_post * `var' ;
	} ; 
	
	foreach var of varlist rpp_* { ;
		gen unfavX`var' = n_age18unfav * `var' ;
	} ;


/* Table for paper/slides */
xi: reg n_3crime2genrt_1997_2017 n_post dobdd_run postXdobdd
		`controls_all' 
		if abs(dobdd_run) <= 264 
		, robust; 
outreg2 using "`output'\\`exp'_rdgeoratetable.xls", e(N r2 rmse) replace ;

xi: reg n_3crime2genrt_1997_2017 n_post dobdd_run postXdobdd
		`controls_all' i.state
		if abs(dobdd_run) <= 264 
		, robust; 
outreg2 using "`output'\\`exp'_rdgeoratetable.xls", e(N r2 rmse) append ;

foreach rate in urate minwage rpp { ;

	xi: reg n_3crime2genrt_1997_2017 postX`rate'_3 n_post `rate'_3 dobdd_run postXdobdd
		`controls_all'
		if abs(dobdd_run) <= 264 
		, robust; 
	outreg2 using "`output'\\`exp'_rdgeoratetable.xls", e(N r2 rmse) append ;
	
	xi: reg n_3crime2genrt_1997_2017 postX`rate'_3 n_post `rate'_3 dobdd_run postXdobdd
		`controls_all' i.state
		if abs(dobdd_run) <= 264 
		, robust; 
	outreg2 using "`output'\\`exp'_rdgeoratetable.xls", e(N r2 rmse) append ;
	
} ;

capture log close ;